<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);
                
// Set flag that this is a parent file.
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
	include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
	define('JPATH_BASE', dirname(__FILE__));
	require_once JPATH_BASE.'/includes/defines.php';
}

require_once JPATH_BASE.'/includes/framework.php';

// Instantiate the application.
$app = JFactory::getApplication('site');

// Initialise the application.
$app->initialise();


require_once JPATH_BASE.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'reportProvider.php';
require_once JPATH_BASE.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'pointProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel.php';

// Init excel
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Wmc")
                             ->setLastModifiedBy("Wmc")
                             ->setTitle("VIP Membership Club Summary Report")
                             ->setSubject("VIP Membership Club Summary Report")
                             ->setDescription("VIP Membership Club Summary Report")
                             ->setKeywords("VIP Membership Club Summary Report")
                             ->setCategory("VIP Membership Club Summary Report");


// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->mergeCells('A1:K1');
$activeSheet->setCellValue('A1', 'VIP Membership Club Summary Report');
$activeSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('A1')->getFont()->setSize(24);
$activeSheet->getStyle('A1')->getFont()->setBold(true);

/************** FILL HEADER **************/
$index = 6;
$activeSheet->setCellValue('A'.$index, 'Member - Recruitment');
setBackgroundColor($activeSheet, 'A', $index, 35);

$activeSheet->setCellValue('A7', 'New Join Member');
$activeSheet->setCellValue('A8', 'PTD Member Total');
$activeSheet->setCellValue('A9', 'No. of Monthly Active Member');
$activeSheet->setCellValue('A10', 'No. of Monthly Inactive Member');
$activeSheet->setCellValue('A11', 'No. of Un-confirmed Member');
// End init excel

$datefrom = explode('-', strval($_GET['f']));
$dateto = explode('-', strval($_GET['t']));

/******** Query Data ********/  
$db = JFactory::getDbo();
$timeDateFrom = mktime(0, 0, 0, $datefrom[1], $datefrom[2], $datefrom[0]);
$timeDateTo = mktime(0, 0, 0, $dateto[1], $dateto[2], $dateto[0]);
$data = array();
if($timeDateFrom <= $timeDateTo)
{
    for($j=$datefrom[0]; $j<=$dateto[0]; $j++) // Year
    {
        for($i=$datefrom[1]; $i<=$dateto[1]; $i++) // Month
        {
            $key = mktime(0, 0, 0, $i, 1, $j);
            $data[date('M.Y', $key)] =  membershipClub($i, $j, $db);
        }   
    }            
}


if(!empty($data))
{    
   // $unConfirmedMemberPrevMonth = 984; // March Month
    $cols = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S');
    $index = 1;
    
    foreach($data as $key => $item){
        
        $activeSheet->setCellValue($cols[$index].'6', $key);
        setBackgroundColor($activeSheet, $cols[$index], '6');
        $activeSheet->setCellValue($cols[$index].'7', $item['new_join_member']);
        $activeSheet->setCellValue($cols[$index].'8', $item['member_total']);
        $activeSheet->setCellValue($cols[$index].'9', $item['active_member']);
        $activeSheet->setCellValue($cols[$index].'10', ($item['member_total']-$item['active_member']));
        
       // $unConfirmedMemberPrevMonth = ($unConfirmedMemberPrevMonth + $item['new_join_member'] - $item['pick_up_member']);
        $activeSheet->setCellValue($cols[$index].'11', number_format(($item['pick_up_member']), 0, '.', '.'));
        
        $index++;
    }
    //$activeSheet->mergeCells('A6:'.$cols[$index-1].'6');
}

$fileName = 'vip_membership_club_'.date('m-d-Y');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_BASE.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$fileName.'.xlsx"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $fsize);
ob_clean();
flush();
readfile( $fullPath );
exit;          

function membershipClub($month, $year, $db)
{
    /******** Query Data ********/
    $query = reportProvider::membershipClubQuery($month, $year);
    $result = array();
    
    $db->setQuery($query['new_join_member']);
    $result['new_join_member'] = $db->loadResult();
    
    $db->setQuery($query['member_total']);
    $result['member_total'] = $db->loadResult();
    
    $db->setQuery($query['active_member']);
    $result['active_member'] = $db->loadObjectList();
    $result['active_member'] = count($result['active_member']);
    
    $db->setQuery($query['pick_up_member']);
    $result['pick_up_member'] = $db->loadResult();
    
    /******** Return Data ********/
    return $result;
}
    
function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15)
{
    $fill = $activeSheet->getStyle($colName.$colIndex)->getFill();
    $fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $fill->getStartColor()->setARGB('cc99ff');  
    $activeSheet->getColumnDimension($colName)->setWidth($width);    
    
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    //$style->getFont()->setBold(true);
    $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
